
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Connect {
	/* Connexion à la base de données */
	String bdd = "serveurjava";
	String table = "actions";
	String url = "jdbc:mysql://localhost:3306/";
	String user = "root";
	String password = "";
	Connection connexion = null;
	Statement statement = null;
	ResultSet resultat = null;
	ArrayList<Object[]> cotes = new ArrayList<Object[]>();
	//uh
	public boolean coMe(String email, String pw){
		boolean coOk = false;
		try {
			connexion = DriverManager.getConnection( url + bdd, user, password );
			statement = connexion.createStatement();
			resultat = statement.executeQuery( "SELECT * FROM users WHERE " +
			"email='" + email + "' AND password='" + pw + "' ;" );
			if(resultat.next()){
				int id = resultat.getInt("id");
				if(id != 0){
					coOk = true;
				}
			}
		}
		catch(SQLException e){ System.out.println(e); }
		return coOk;
	}	
	public boolean isEmailExists(String email){
		boolean coOk = false;
		try {
			connexion = DriverManager.getConnection( url + bdd, user, password );
			statement = connexion.createStatement();
			resultat = statement.executeQuery( "SELECT * FROM users WHERE " +
			"email='" + email + "' ;" );
			while(resultat.next()){
				int id = resultat.getInt("id");
				if(id != 0){
					coOk = true;
				}
			}
		}
		catch(SQLException e){ System.out.println(e); }
		return coOk;
	}

	public ArrayList<Object[]> getCotes(int id){
		Object[] cote = {"","","","","","",""};
		try {
			connexion = DriverManager.getConnection(url + bdd, user, password);
			statement = connexion.createStatement();
			resultat = statement.executeQuery("SELECT P.*, C.* FROM paris AS P, cotes AS C " + 
			"WHERE C.fk_paris = P.id" + " AND P.date > NOW() ORDER BY P.date ;");
			while(resultat.next()){
				cote = new Object[] {resultat.getInt("id"),
				resultat.getString("equipe1"),
				"match nul",
				resultat.getString("equipe2"),
				resultat.getFloat("cote_e1"),
				resultat.getFloat("cote_n"),
				resultat.getFloat("cote_e2")};
				cotes.add(cote);
			}
		} catch (SQLException f) { System.out.print(f + "\n"); }
		return cotes;
	}

	public void listed(String localhost) {
		try {
			connexion = DriverManager.getConnection(url + bdd, user, password);
			statement = connexion.createStatement();
			String sql = "INSERT INTO blacklist (localhost) VALUES('" + localhost + "') ;";
			statement.executeUpdate(sql);
		} catch (SQLException f) { System.out.print(f + "\n"); }
	}

	public List<String> getBlackListed() {
		List<String> listed = new ArrayList<String>();
		try {
			connexion = DriverManager.getConnection(url + bdd, user, password);
			statement = connexion.createStatement();
			resultat = statement.executeQuery("SELECT * FROM blacklist;");
			while (resultat.next()) {
				String lh = resultat.getString("localhost");
				if (lh != null & !lh.isEmpty()) {
					listed.add(lh);
				}
			}
			return listed;
		} catch (SQLException e) { System.out.println(e); }
		return listed;
	}

	public String getPw(String email) {
		try {
			connexion = DriverManager.getConnection(url + bdd, user, password);
			statement = connexion.createStatement();
			resultat = statement.executeQuery("SELECT password FROM users WHERE email='" + email + "' ;");
			if (resultat.next()) {
				String pw = resultat.getString("password");
				if (pw != null & !pw.isEmpty()) {
					return pw;
				}
			}
		} catch (SQLException e) { System.out.println(e); }
		return null;
	}

	public void insertUser(String email, String pw) {
		try {
			connexion = DriverManager.getConnection(url + bdd, user, password);
			statement = connexion.createStatement();
			String sql = "INSERT INTO users (email, password, credit) VALUES('" + email + "', '" + pw + "', 0) ;";
			statement.executeUpdate(sql);
		} catch (SQLException f) { System.out.print(f + "\n"); }
	}	
	public void modifSolde(float solde, String email) {
		try {
			connexion = DriverManager.getConnection(url + bdd, user, password);
			statement = connexion.createStatement();
			String sql = "UPDATE users SET credit = "+ solde +"  WHERE email = '" + email + "' ;";
			statement.executeUpdate(sql);
		} catch (SQLException f) { System.out.print(f + "\n"); }
	}
	public void insertLog(String email, int idf, int pays, float bet, float rate) {
		try {
			connexion = DriverManager.getConnection(url + bdd, user, password);
			statement = connexion.createStatement();
			String sql = "INSERT INTO log(fk_user, fk_paris, equipe, bet, rate, validate) VALUES("+
				" '"+email+"', "+idf+", '"+pays+"', "+bet+", "+rate+", 0 "+
				");";
			statement.executeUpdate(sql);
		} catch (SQLException f) { System.out.print(f + "\n"); }
	}

	public ArrayList<Object[]> getUserInfos(String email) {
		ArrayList<Object[]> userInfos = new ArrayList<Object[]>();
		Object[] infos = {"","","",""};
		try {
			connexion = DriverManager.getConnection(url + bdd, user, password);
			statement = connexion.createStatement();
			resultat = statement.executeQuery("SELECT * FROM users WHERE email='" + email + "' ;");
			if (resultat.next()) {
				infos = new Object[] {
					resultat.getInt("id"),
					resultat.getString("email"),
					resultat.getString("password"),
					resultat.getFloat("credit")};
				userInfos.add(infos);
			}
		} catch (SQLException e) { System.out.println(e); }
		return userInfos;
	}

	public void getBetsOnline(String email, float solde) {
		try {
			connexion = DriverManager.getConnection(url + bdd, user, password);
			statement = connexion.createStatement();
			resultat = statement.executeQuery("SELECT L.*, L.id AS logid, P.*, P.id AS parisid FROM log AS L, paris AS P "+
				"WHERE L.fk_user='" + email + "' AND L.fk_paris = P.id AND P.date < NOW() AND L.validate = 0 ;");
			ArrayList<String> sql = new ArrayList<String>();
			while (resultat.next()) {
				int id = resultat.getInt("logid");
				float rate = resultat.getFloat("rate");
				float bet = resultat.getFloat("bet");
				int equipe = resultat.getInt("equipe");
				int win = resultat.getInt("win");
				if(win == equipe){
					solde += rate * bet;
				}
				int validate = 1;
				sql.add("UPDATE log SET validate = " + validate + " WHERE id = " + id + " ;");
			}
			System.out.println(solde);
			sql.add("UPDATE users SET credit = " + solde + " WHERE email = '" + email + "' ;");

			for(int i = 0; i < sql.size(); i++){
				statement.executeUpdate(sql.get(i));
			}
		} catch (SQLException e) { System.out.println(e); }
	}

	public void createDb() {
		try {
			connexion = DriverManager.getConnection(url + "?user=" + user + "&password=" + password + "");
			statement = connexion.createStatement();
			statement.executeUpdate("CREATE DATABASE IF NOT EXISTS " + bdd);
			statement.executeUpdate("USE " + bdd);
			String sql = "CREATE TABLE IF NOT EXISTS users "
			+ "(id INTEGER not NULL AUTO_INCREMENT, "
			+ " email VARCHAR(255), "
			+ " password VARCHAR(255), "
			+ " credit float, "
			+ " PRIMARY KEY ( id ))";
			statement.executeUpdate(sql);
			sql = "CREATE TABLE IF NOT EXISTS blacklist "
			+ "(id INTEGER not NULL AUTO_INCREMENT, "
			+ " localhost VARCHAR(255), "
			+ " PRIMARY KEY ( id ))";
			statement.executeUpdate(sql);
			sql = "CREATE TABLE IF NOT EXISTS paris "
			+ "(id INTEGER not NULL AUTO_INCREMENT, "
			+ " equipe1 VARCHAR(255), "
			+ " equipe2 VARCHAR(255), "
			+ " date DATETIME NOT NULL, "
			+ " win INT(11), "
			+ " PRIMARY KEY ( id ))";
			statement.executeUpdate(sql);
			sql = "CREATE TABLE IF NOT EXISTS cotes "
			+ "(fk_paris INT(11), "
			+ " cote_e1 FLOAT, "
			+ " cote_n FLOAT, "
			+ " cote_e2 FLOAT "
			+ " )";
			statement.executeUpdate(sql);
			sql = "CREATE TABLE IF NOT EXISTS log (id INTEGER not NULL AUTO_INCREMENT,"+
				"fk_user VARCHAR(255),"+
				"fk_paris INT(11),"+
				"equipe INT(11)," +
				"bet FLOAT," +
				"rate FLOAT," +
				"validate INT(11), "+
				"PRIMARY KEY ( id ))";
			statement.executeUpdate(sql);
		} catch (SQLException f) {
		System.out.print(f + "\n");
		}
	}

}
